using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.Linq;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using NUnit.Framework;
using WW.NActiveRecord.Tests;
using WW.NActiveRecord.Tests.Models;

namespace WW.NActiveRecord.Tests{
    [TestFixture]
    public class TestEntitySet :BaseTest {
        [TestFixtureSetUp]
        public void SetUpFixture(){
            base.SetUpDb(Table_Employees,Table_Products,Table_Categories);
        }
        
        [Test]
        public void TestSelect()
        {
            Product.All.Sql().ShouldBe("SELECT Products.* FROM Products");
            Product.All.Select("ProductName").Sql().ShouldBe("SELECT ProductName FROM Products");
            Product.All.Select("UnitPrice").Select("ProductName").Sql().ShouldBe("SELECT ProductName FROM Products");

            Employee.All.Select("firstName,lastName")
                .Where("firstName = @firstName and lastName=@lastName", @firstName => "Nancy", @lastName => "Davolio")
                .First().ToJson().ShouldBe("{firstName:'Nancy',lastName:'Davolio'}");

            Employee.All.Select("firstName,lastName")
                .SelectMore("(lastName +' '+ firstName ) as name")
                .Where("firstName = @firstName and lastName=@lastName", @firstName => "Nancy", @lastName => "Davolio")
                .First()
                .ToJson().ShouldBe(@"{firstName:'Nancy',lastName:'Davolio',name:'Davolio Nancy'}");
        }


        [Test]
        public void TestSelectMore()
        {
            Product.All.SelectMore("ProductName AS ProductName2").Sql()
                .ShouldBe("SELECT Products.*,ProductName AS ProductName2 FROM Products");
        }


        [Test]
        public void TestWhereUsingSql()
        {
            Employee.All.Where("firstName='Nancy' AND lastName='Davolio'")
                           .Sql().ShouldBe("SELECT Employees.* FROM Employees WHERE firstName='Nancy' AND lastName='Davolio'");

            Employee.All.Where("firstName=@firstName AND lastName=@lastName", firstName => "Nancy", lastName => "Davolio")
               .Sql().ShouldBe("SELECT Employees.* FROM Employees WHERE firstName='Nancy' AND lastName='Davolio'");

            Employee.All.Where("firstName=@firstName", firstName => "Nancy")
                .Where("lastName=@lastName", lastName => "Davolio")
               .Sql().ShouldBe("SELECT Employees.* FROM Employees WHERE firstName='Nancy' AND lastName='Davolio'");

            //null value
            Employee.All.Where("firstName=@firstName", firstName => null)
               .Sql().ShouldBe("SELECT Employees.* FROM Employees WHERE firstName=NULL");


            var result = Employee.All
                .Where("firstName = @firstName and lastName=@lastName", @firstName => "Nancy", lastName => "Davolio");
            result.Count().ShouldBe(1);
            result.First().FirstName.ShouldBe("Nancy");
        }


        [Test]
        public void TestWhereUsingSql_DynamicParameter() {
            string name = "Nancy";
            EntitySet<Employee> e= Employee.All.Where("firstName=@firstName", firstName => name);
            e.Sql().ShouldBe("SELECT Employees.* FROM Employees WHERE firstName='Nancy'");
 
            name = "chen peng";//changed
 
            e.Sql().ShouldBe("SELECT Employees.* FROM Employees WHERE firstName='chen peng'");
        }

        [Test]public void TestWhereUsingAHash()
        {
            Employee.All.Where(firstName => "Nancy", lastName => "Davolio")
               .Sql().ShouldBe("SELECT Employees.* FROM Employees WHERE firstName='Nancy' AND lastName='Davolio'");

            //null value
            Employee.All.Where(firstName => null)
               .Sql().ShouldBe("SELECT Employees.* FROM Employees WHERE firstName IS NULL");

            //collection
            Employee.All.Where(City => new[] { "Seattle", "London" })
               .Sql().ShouldBe("SELECT Employees.* FROM Employees WHERE City IN ('Seattle','London')");

            var result = Employee.All.Where(lastName => "Davolio");
            result.Count().ShouldBe(1);
            result.First().FirstName.ShouldBe("Nancy");
        }


        [Test]
        public void TestWhereUsingAHash_DynamicParameter() {
            string name = "Nancy";
            EntitySet<Employee> e = Employee.All.Where(firstName => name);
            e.Sql().ShouldBe("SELECT Employees.* FROM Employees WHERE firstName='Nancy'");

            name = "chen peng";//changed

            e.Sql().ShouldBe("SELECT Employees.* FROM Employees WHERE firstName='chen peng'");
        }

        [Test]
        public void TestFrom()
        {
            Product.All.From("Categories").Sql()
                .ShouldBe("SELECT Products.* FROM Products,Categories");

            Product.All.From("Categories")
                .Where("Products.CategoryID=Categories.CategoryID")
                .Sql()
                .ShouldBe("SELECT Products.* FROM Products,Categories WHERE Products.CategoryID=Categories.CategoryID");

            Employee.All.Select("Employees.firstName,e2.firstName as boss")
                .From("Employees e2")
                .Where("Employees.firstName = 'Nancy' AND Employees.ReportsTo = e2.EmployeeID")
                
                .ShouldBeJson("[{firstName:'Nancy',boss:'Andrew'}]");
        }

        [Test]
        public void TestAlias(){
            Product.All.From(c => "Categories").Sql().ShouldBe("SELECT Products.* FROM Products,Categories AS c");
            Product.All.From(c => "Categories", s => "Suppliers").Sql().ShouldBe("SELECT Products.* FROM Products,Categories AS c,Suppliers AS s");


            Product.As("e").Sql().ShouldBe("SELECT e.* FROM Products AS e");

            Product.As("e").From(s => "(SELECT * FROM Suppliers)", c => "Categories")
                .Sql().ShouldBe("SELECT e.* FROM Products AS e,(SELECT * FROM Suppliers) AS s,Categories AS c");

            Product.As("e").From(s => "Suppliers", c => "Categories")
                .Where("e.CategoryID=c.CategoryID")
                .Where("e.SupplierID=s.SupplierID")
                .Sql().ShouldBe("SELECT e.* FROM Products AS e,Suppliers AS s,Categories AS c WHERE e.CategoryID=c.CategoryID AND e.SupplierID=s.SupplierID");
        }

        [Test]
        public void TestLeftJoin()
        {
            Product.As("p")
                .LeftJoin(s => "(SELECT * FROM Suppliers)", "p.SupplierID=s.SupplierID")
                .LeftJoin(c => "Categories","p.CategoryID=c.CategoryID")
                .Sql().ShouldBe("SELECT p.* FROM Products AS p "+
                                "LEFT JOIN (SELECT * FROM Suppliers) AS s ON p.SupplierID=s.SupplierID " +
                                "LEFT JOIN Categories AS c ON p.CategoryID=c.CategoryID");
        }

        [Test]
        public void TestOrderBy() {
            Product.All.OrderBy("UnitPrice DESC,ProductName")
                .Sql().ShouldBe("SELECT Products.* FROM Products ORDER BY UnitPrice DESC,ProductName");
        }
  

         
        

        [Test]
        public void TestGroupByHaving(){
            //Product.All.Select("CategoryID", "SUM(UnitPrice) AS UnitPrice").GroupBy("CategoryID").Having("SUM(LineTotal) > $1000000.00")
           //     .Sql().ShouldBe("SELECT CategoryID,SUM(UnitPrice) AS UnitPrice FROM Products GROUP BY CategoryID");
        }



    }
}